from mtmodel.utils import store, pd_source
from mtmodel.model import ranking
from mtmodel.model import preprocessing
from tqdm import tqdm
import time

def score_global():
    sql = """
    SELECT a.source, a.game_id, a.game_name,
    (case
    a.score when 0 then 0
    else
    (a.5_star * 5 + a.4_star * 4 + a.3_star * 3 + a.2_star * 2 + a.1_star * 1) / (a.5_star + a.4_star + a.3_star + a.2_star + a.1_star) * 2
    end) score,
    a.total,
    cast(unix_timestamp(a.updated_at) as signed) as updated_at
    FROM game_source.s_game_comment_number a join
    (SELECT source,game_id,max(updated_at), updated_at
    FROM game_source.s_game_comment_number where source='taptap' GROUP BY source,game_id) b
    on a.source = b.source and a.game_id = b.game_id and a.updated_at = b.updated_at
    """
    df = pd_source.from_sql(sql)
    avg_score = df['score'].sum() / len(df)
    avg_total = df['total'].sum() / len(df)
    df3 = df[['source', 'game_id', 'game_name', 'score', 'total']]
    df3['bma_score'] = df.apply(lambda s: ranking.bma(s['total'], avg_total, s['score'], avg_score), axis = 1)
    df3 = df3.sort_values(by=['bma_score'], ascending=False).reset_index()
    print("starting store")
    for no in tqdm(df3.index):
        row = df3.iloc[no]
        kv = dict(
            no = str(no),
            source=str(row['source']),
            game_id=str(row['game_id']),
            game_name=str(row['game_name']),
            score=str(row['score']),
            total=str(row['total']),
            bma_score=str(row['bma_score'])
            )
        store.to_mysql(kv, 'game_process', 'c_game_ranking_by_bma_taptap')

    # max_score = 10.0
    # df4 = df[['source', 'game_id', 'game_name', 'score', 'total']]
    # df4['wilson_score'] = df.apply(lambda s: ranking.wilson_score(s['score'] / max_score, s['total']), axis = 1)




def score_weekly():
    seven_date=time.strftime("%Y-%m-%d", time.localtime( time.time() - 7 * 86400))
    sql = """
    SELECT a.source, a.game_id, a.game_name,
    (case
    a.score when 0 then 0
    else
    (a.5_star * 5 + a.4_star * 4 + a.3_star * 3 + a.2_star * 2 + a.1_star * 1) / (a.5_star + a.4_star + a.3_star + a.2_star + a.1_star) * 2
    end) score,
    a.total, a.updated_at, b.min_update, b.max_update
    FROM game_source.s_game_comment_number a join
    (select source,game_id, max(updated_at) max_update, min(updated_at) min_update from game_source.s_game_comment_number
    WHERE source='taptap' AND updated_at >= '%s' GROUP BY source,game_id) b
    USING(source, game_id)
    WHERE (a.updated_at = b.min_update OR a.updated_at = b.max_update) AND (b.min_update <> b.max_update)
    """ % (seven_date, )
    df2 = pd_source.from_sql(sql)
    df2_min = df2[df2['updated_at'] == df2['min_update']].reset_index(drop=True)
    df2_max = df2[df2['updated_at'] == df2['max_update']].reset_index(drop=True)


    df3 = df2_max[['source', 'game_id', 'game_name', 'score', 'total']]
    df3['total_week'] = df2_max['total'] - df2_min['total']
    df3['score_sum_week'] = (df2_max['score'] * df2_max['total'] - df2_min['score'] * df2_min['total'])
    df3 = df3[(df3['total_week'] > 0.0) & (df3['score_sum_week'] > 0.0)]
    df3['p'] = (df3['score_sum_week'] / df3['total_week'] / 10).apply(lambda x:min(x,1.0))
    df3['wilson_score'] = df3.apply(lambda s: ranking.wilson_score(s['p'], s['total_week']), axis = 1)


    print("starting store")
    df4 = df3.sort_values(by=['total_week'], ascending=False).reset_index(drop=True)
    for no in tqdm(df4.index):
        row = df4.iloc[no]
        kv = dict(
            no = str(no),
            source=str(row['source']),
            game_id=str(row['game_id']),
            game_name=str(row['game_name']),
            score=str(row['score']),
            total=str(row['total']),
            total_week=str(row['total_week'])
            )
        store.to_mysql(kv, 'game_process', 'c_game_ranking_by_weekly_comments_taptap')

    df5 = df3.sort_values(by=['wilson_score'], ascending=False).reset_index(drop=True)
    for no in tqdm(df5.index):
        row = df5.iloc[no]
        kv = dict(
            no = str(no),
            source=str(row['source']),
            game_id=str(row['game_id']),
            game_name=str(row['game_name']),
            score=str(row['score']),
            total=str(row['total']),
            total_week=str(row['total_week']),
            wilson_score=str(row['wilson_score'])
            )
        store.to_mysql(kv, 'game_process', 'c_game_ranking_by_wilson_score_taptap')

def main():
    # score_global()
    score_weekly()

if __name__ == '__main__':
    main()